/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package bo2s.DAO;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import bo2s.data.User;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author thanhap
 */
public class UserDAO {

    private static Connection conn = null;
    PreparedStatement pstmt = null;
    Statement stmt = null;

    public static boolean Login(String username, String password) {

        try {
            conn = new ConnectionManager().getConnection();
            PreparedStatement pst = conn.prepareStatement("select * from user where username=? and password=?");
            pst.setString(1, username);
            pst.setString(2, password);
            ResultSet rs = pst.executeQuery();
            if (rs.next()) {
                return true;
            }

        } catch (Exception e) {
            //System.out.println(e);
        }
        return false;

    }

    public void addNew(User e) {
        //Connection con = null;
        //PreparedStatement pstmt = null;
        try {
            conn = new ConnectionManager().getConnection();
            pstmt = conn.prepareStatement("INSERT INTO user(id,username,password,fullname,email,phone,address,gender,image,role) VALUES(?,?,?,?,?,?,?,?,?,?)");
            pstmt.setInt(1, e.getId());
            pstmt.setString(2, e.getUsername());
            pstmt.setString(3, e.getPassword());
            pstmt.setString(4, e.getFullname());
            pstmt.setString(5, e.getEmail());
            pstmt.setString(6, e.getPhone());
            pstmt.setString(7, e.getAddress());
            pstmt.setString(8, e.getGender());
            pstmt.setString(9, e.getImage());
            pstmt.setInt(10, e.getRole());
            pstmt.execute();
        } catch (SQLException | ClassNotFoundException ex) {
        } catch (InstantiationException ex) {
            Logger.getLogger(UserDAO.class.getName()).log(Level.SEVERE, null, ex);
        } catch (IllegalAccessException ex) {
            Logger.getLogger(UserDAO.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                if (pstmt != null) {
                    pstmt.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException ex) {
                Logger.getLogger(UserDAO.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
    }

    public void delete(int id) {
        try {
            conn = new ConnectionManager().getConnection();
            stmt = conn.createStatement();
            stmt.execute("DELETE FROM user WHERE id=" + String.valueOf(id));
        } catch (SQLException | ClassNotFoundException ex) {

        } catch (InstantiationException ex) {
            Logger.getLogger(UserDAO.class.getName()).log(Level.SEVERE, null, ex);
        } catch (IllegalAccessException ex) {
            Logger.getLogger(UserDAO.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                if (stmt != null) {
                    stmt.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException ex) {
                Logger.getLogger(UserDAO.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
    }

    public void update(User e) {
        try {
            pstmt = conn.prepareStatement("UPDATE user SET username=?, password=?, fullname=?, email=?, phone=?, address=?, gender=?, image=?, role=? WHERE id=?");
            pstmt.setString(1, e.getUsername());
            pstmt.setString(2, e.getPassword());
            pstmt.setString(3, e.getFullname());
            pstmt.setString(4, e.getEmail());
            pstmt.setString(5, e.getPhone());
            pstmt.setString(6, e.getAddress());
            pstmt.setString(7, e.getGender());
            pstmt.setString(8, e.getImage());
            pstmt.setInt(9, e.getRole());
            pstmt.setInt(10, e.getId());
            pstmt.executeUpdate();
        } catch (SQLException ex) {

        } finally {
            try {
                if (pstmt != null) {
                    pstmt.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException ex) {
                Logger.getLogger(UserDAO.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
    }

    public User getUser(int id) {
        User usd = null;
        try {
            stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery("SELECT * FROM user WHERE id=" + id);
            if (rs.next()) {
                usd = new User();
                usd.setId(rs.getInt(1));
                usd.setUsername(rs.getString(2));
                usd.setPassword(rs.getString(3));
                usd.setFullname(rs.getString(4));
                usd.setEmail(rs.getString(5));
                usd.setPhone(rs.getString(6));
                usd.setAddress(rs.getString(7));
                usd.setGender(rs.getString(8));
                usd.setImage(rs.getString(9));
                usd.setRole(rs.getInt(10));
            }
        } catch (SQLException ex) {

        } finally {
            try {
                if (stmt != null) {
                    stmt.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException ex) {
                Logger.getLogger(UserDAO.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        return usd;
    }

    public List<User> getUseres() {
        List<User> list = new ArrayList<>();
        try {
            stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery("SELECT * FROM user ORDER BY id");
            while (rs.next()) {
                User usd = new User();
                usd.setId(rs.getInt(1));
                usd.setUsername(rs.getString(2));
                usd.setPassword(rs.getString(3));
                usd.setFullname(rs.getString(4));
                usd.setEmail(rs.getString(5));
                usd.setPhone(rs.getString(6));
                usd.setAddress(rs.getString(7));
                usd.setGender(rs.getString(8));
                usd.setImage(rs.getString(9));
                usd.setRole(rs.getInt(10));
                list.add(usd);
            }
        } catch (SQLException ex) {

        } finally {
            try {
                if (stmt != null) {
                    stmt.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException ex) {

            }
        }
        return list;
    }

}
